In [1]:
from matplotlib import pyplot as plt
%matplotlib inline

import pandas as pd
import numpy as np
from os import sys

from IPython.display import clear_output

from datetime import datetime, timedelta

In [2]:
data_path = "/Users/toni/data/prop-39-school/"

Extract relevant metadata


In [3]:
ann_reps = pd.read_excel(data_path + "PEPS_Data.xlsx", sheetname="Data- Annual Progress Reports")
app_eeps = pd.read_excel(data_path + "PEPS_Data.xlsx", sheetname="Data- Approved EEPs")
fin_reps = pd.read_excel(data_path + "PEPS_Data.xlsx", sheetname="Data- Final Reports")

In [10]:
app_eeps.shape, ann_reps.shape, fin_reps.shape


Out[10]:
((11577, 99), (4576, 28), (230, 97))

In [4]:
ann_reps.iloc[0]


Out[4]:
Local Education Agency (LEA)                                     Cecil Avenue Math & Science Academy
LEA Code                                                                              15634046009351
LEA Address                                                                         1405 12th Avenue
LEA City                                                                                      Delano
LEA ZIP Code                                                                                   93215
Energy Expenditure Plan (EEP) ID Number                                                           28
EEP Submission Fiscal Year                                                                   2014-15
Reporting ID Number                                                                               18
Grant Amount Spent for Energy Manager                                                            NaN
Grant Amount Spent for Training                                                                  NaN
Total Spent on Project Cost                                                                   282756
Total Prop 39 Share Spent on Project Implementation                                           243156
Site Name                                                                Cecil Avenue Charter School
Site CDS Code                                                                         15634046009351
Site Address                                                                       1430 Cecil Avenue
Site City                                                                                     Delano
Site ZIP Code                                                                                  93215
Total Spent for Implementation at Site                                                        282756
Total Prop 39 Funds Spent for Implementation at Site                                          243156
Prop 39 Funds Spent for all EEMs at Site                                                      243156
Prop 39 Funds Spent for all PV Measures at Site                                                  NaN
Prop 39 Funds Spent for all PPA Measures at Site                                                 NaN
Energy Efficiency Measure (EEM)                         HVAC- Packaged/Split System AC/Heat Pump/VRF
Amount Spent for EEM                                                                          253147
AC Power Rating of the LEA Owned PV System (kW)                                                  NaN
Amount Spent for PV Measure                                                                      NaN
PPA PV System Size                                                                               NaN
Amount Spent for PPA Measure                                                                     NaN
Name: 0, dtype: object

In [5]:
app_eeps.iloc[0]


Out[5]:
Local Education Agency (LEA)                                                                      Alameda County Office of Education
LEA Code                                                                                                               1100170000000
LEA Mailing Address                                                                                                313 W. Winton Ave
LEA City                                                                                                                     Hayward
LEA ZIP Code                                                                                                                   94544
Energy Expenditure Plan (EEP) ID Number                                                                                            1
EEP Submission Fiscal Year                                                                                                   2013-14
Grant Amount Requested Based on Single Year Allocation or Multiple Years of Allocation?         Annual Award Energy Expenditure Plan
Grant Amount Requested (Prop 39 Shares for all sites plus Energy Manager & Training funds)                                     84921
Grant Amount Requested through EEP for Energy Manager                                                                          56781
Grant Amount Requested Through EEP for Training                                                                                  NaN
Total Project Cost for Project Measure Implementation                                                                          28140
LEA Tier Classification                                                                                                          NaN
Were Planning Funds Requested from California Department of Education?                                                           Yes
Budget for Screening and Energy Audits Over Program Life (CDE Planning Funds)                                                    NaN
Budget for Prop 39 Program Assistance Over Program Life (CDE Planning Funds)                                                     NaN
Budget for Energy Manager Over Program Life (CDE Planning Funds)                                                               56781
Budget for Training Over Program Life (CDE Planning Funds)                                                                       NaN
LEA-Wide Savings to Investment Ratio (SIR) for EEP                                                                             2.573
Site Name                                                                                                               Burke Center
Site CDS Code                                                                                                            1.10017e+12
Site Mailing Address                                                                                                313 W.Winton Ave
Site City                                                                                                                    Hayward
Site ZIP Code                                                                                                                  94544
Senate District                                                                                                                   10
Assembly District                                                                                                                 20
Site Conditioned Area, Square Feet (SF)                                                                                         6555
Site Electric Utility                                                                                                           PG&E
Site Natural Gas Utility                                                                                                        PG&E
Average Peak Demand (kW)                                                                                                         NaN
                                                                                                                ...                 
EEM Estimated Annual Propane Savings (Gal)                                                                                       NaN
EEM Estimated Annual Fuel Oil Savings (Gal)                                                                                      NaN
EEM Estimated Annual Energy Cost Savings                                                                                        2244
EEM Estimated Measure Cost                                                                                                     25380
Estimated Rebates for EEM                                                                                                        NaN
Estimated Other Non-Repayable Funds for EEM                                                                                      NaN
Estimated Total Rebates Plus Other Non-Repayable Funds for EEM                                                                  3455
EEM SIR                                                                                                                         1.49
AC Power Rating of the LEA Owned PV System (kW)                                                                                  NaN
Estimated Effective Useful Life of PV Measure (years)                                                                            NaN
Estimated Demand Savings for PV Measure (kW)                                                                                     NaN
Estimated First Year Annual Electricity Production of PV Measure (kWh)                                                           NaN
Estimated First Year Annual Energy Cost Savings for PV Measure                                                                   NaN
Estimated PV Measure Cost                                                                                                        NaN
Estimated PV Measure Rebates                                                                                                     NaN
Estimated PV Measure Other Non-Repayable Funds                                                                                   NaN
Estimated Total Rebates Plus Other Non-Repayable Funds for PV Measure                                                            NaN
PV Measure SIR                                                                                                                   NaN
AC Power Rating of PPA Measure generation system (kW)                                                                            NaN
Estimated Site Demand Savings of PPA Measure (kW)                                                                                NaN
Estimated First Year Electricity Production of PPA Measure Generation System (kWh)                                               NaN
Term of PPA Measure Agreement                                                                                                    NaN
Estimated PPA Measure Electricity Generation as Percentage of Baseline Electricity Usage (%)                                     NaN
Estimated First Year Electricity Cost ($/kWh)                                                                                    NaN
Electricity Price Discount Offered in First Year (%)                                                                             NaN
PPA Measure Electricity Price Escalation Rate (%)                                                                                NaN
Net Present Value of Utility Cost Savings                                                                                        NaN
Net Present Value of Payment to PPA Vendor                                                                                       NaN
Net Present Value of Prop 39 Funds for PPA Measure                                                                               NaN
PPA Measure SIR                                                                                                                  NaN
Name: 0, dtype: object

In [8]:
fin_reps.iloc[0]


Out[8]:
Local Education Agency (LEA)                                                                          Santa Ana Unified School District
LEA Code                                                                                                                 30666700000000
LEA Address                                                                                                     1601 E. Chestnut Avenue
LEA City                                                                                                                      Santa Ana
LEA ZIP Code                                                                                                                      92701
Energy Expenditure Plan (EEP) ID Number                                                                                              60
EEP Submission Fiscal Year                                                                                                      2013-14
LEA Project Completion Date                                                                                         2014-09-01 00:00:00
Reported Grant Amount Spent (Prop 39 Shares for all measures plus Energy Manager & Training funds)                          1.50066e+06
EEP Grant Amount Spent for Energy Manager                                                                                           NaN
EEP Grant Amount Spent for Training                                                                                                 NaN
Amount Spent for Screening and Audits                                                                                             86078
Total Project Implementation Cost                                                                                           1.50264e+06
Reported Total Prop 39 Share Spent on Project Implementation                                                                1.50066e+06
CDE Planning Funds Spent for Program Assistance                                                                                   39656
CDE Planning Funds Spent for Energy Manager                                                                                      121229
CDE Planning Funds Spent for Training                                                                                               NaN
Total Planning Funds Spent                                                                                                       246963
LEA-Wide Savings to Investment Ratio (SIR)                                                                                      1.10318
Site Name                                                                                                             Harvey Elementary
Site CDS Code                                                                                                            30666706030225
Site Address                                                                                                   1635 South Center Street
Site City                                                                                                                     Santa Ana
Site ZIP Code                                                                                                                     92704
Site Conditioned Area, Square Feet (SF)                                                                                           48122
Average Peak Demand (kW)                                                                                                            177
Total Annual Electric Use for Site (kWh)                                                                                         449955
Annual On Site Generation (kWh)                                                                                                       0
Annual Electricity Purchase from Utility (kWh)                                                                                   449955
Annual Combined Electric Charges                                                                                                97376.9
                                                                                                                    ...                
Reported Annual Fuel Oil Savings at Site (therms)                                                                                     0
Reported Total Annual Cost Savings at Site                                                                                      37414.8
Measure Cost                                                                                                                     517232
EEM Rebates & Grants                                                                                                                925
Other Non-Repayable Funds for EEM                                                                                                   NaN
EEM Rebates                                                                                                                         925
EEM-Level SIR                                                                                                                      1.27
AC Power Rating of the LEA Owned PV System (kW)                                                                                     NaN
Reported Demand Savings for PV Measure (kW)                                                                                         NaN
Reported First Year Annual Electricity Production of PV Measure (kWh)                                                               NaN
Stated Effective Useful Life of PV Measure (years)                                                                                  NaN
Reported First Year Electricity Cost Savings                                                                                        NaN
Reported Inverter Size (kW)                                                                                                         NaN
Reported PV Measure Cost                                                                                                            NaN
PV Measure Rebates                                                                                                                  NaN
PV Measure Other Non-Repayable Funds                                                                                                NaN
Total Rebates Plus Other Non-Repayable Funds for PV Measure                                                                         NaN
PV Measure SIR                                                                                                                      NaN
AC Power Rating of PPA Measure generation system (kW)                                                                               NaN
Reported Site Demand Savings of PPA Measure (kW)                                                                                    NaN
Reported First Year Electricity Production of PPA Measure Generation System (kWh)                                                   NaN
Term of PPA Measure Agreement (years)                                                                                               NaN
Reported First Year Electricity Use of PPA Measure Generation System (kWh)                                                          NaN
Reported First Year Electricity Cost                                                                                                NaN
PPA Discount Offered on Price First Year                                                                                            NaN
PPA Price Escalation (%)                                                                                                            NaN
NPV of Utility Cost Savings                                                                                                         NaN
NPV of Payment to PPA Vendor                                                                                                        NaN
NPV of Prop 39 Contribution                                                                                                         NaN
PPA Measure SIR                                                                                                                     NaN
Name: 0, dtype: object

In [24]:
fields = ["Site CDS Code", "Site Name", "Site Address", "Site City", "Site Mailing Address", "Site ZIP Code", 
          "Energy Efficiency Measure (EEM)"]
fin_reps[[c for c in fields if c in fin_reps.columns]]


Out[24]:
Site CDS Code Site Name Site Address Site City Site ZIP Code
0 30666706030225 Harvey Elementary 1635 South Center Street Santa Ana 92704
1 30666706030225 Harvey Elementary 1635 South Center Street Santa Ana 92704
2 30666706030225 Harvey Elementary 1635 South Center Street Santa Ana 92704
3 30666706030365 Monte Vista Elementary 2116 W Monte Vista Santa Ana 92704
4 30666706030365 Monte Vista Elementary 2116 W Monte Vista Santa Ana 92704
5 30666706107692 Kennedy Elementary 1300 East McFadden Avenue Santa Ana 92705
6 30666706107692 Kennedy Elementary 1300 East McFadden Avenue Santa Ana 92705
7 30666706107692 Kennedy Elementary 1300 East McFadden Avenue Santa Ana 92705
8 43733874334470 Milpitas High School 1285 Escuela Parkway Milpitas 95035
9 43733876047583 Rose Elementary School 250 Roswell Drive Milpitas 95035
10 37680236116859 Arroyo Vista Charter 2491 School House Road Chula Vista 91915
11 37680236111322 Discovery Charter 1100 Camino Biscay Chula Vista 91910
12 41689656044168 Laurel School 95 Edge Road Atherton 94027
13 33671576032353 Nuview Elementary School 29680 Lakeview Ave. Nuevo, Riverside County 92567
14 33671576032353 Nuview Elementary School 29680 Lakeview Ave. Nuevo, Riverside County 92567
15 33671576032353 Nuview Elementary School 29680 Lakeview Ave. Nuevo, Riverside County 92567
16 37680236037824 Castle Park ES 25 Emerson Street Chula Vista 91911
17 37680236037824 Castle Park ES 25 Emerson Street Chula Vista 91911
18 37680236037824 Castle Park ES 25 Emerson Street Chula Vista 91911
19 37680236037931 Rice ES 915 Fourth Avenue Chula Vista 91911
20 37680236037931 Rice ES 915 Fourth Avenue Chula Vista 91911
21 37680236037931 Rice ES 915 Fourth Avenue Chula Vista 91911
22 37680236037998 Rosebank ES 80 Flower Street Chula Vista 91910
23 37680236037998 Rosebank ES 80 Flower Street Chula Vista 91910
24 37680236037998 Rosebank ES 80 Flower Street Chula Vista 91910
25 37680236037998 Rosebank ES 80 Flower Street Chula Vista 91910
26 20652012032357 Chowchilla High School 805 Humboldt Ave Chowchilla 93610-3117
27 20652012032357 Chowchilla High School 805 Humboldt Ave Chowchilla 93610-3117
28 20652012032357 Chowchilla High School 805 Humboldt Ave Chowchilla 93610-3117
29 30664986027916 Fulton Middle School 8778 El Lago Circle Fountain Valley 92708
... ... ... ... ... ...
200 13631646008569 Frank M. Wright Middle 885 North Imperial Ave. Imperial 92251
201 13631646008569 Frank M. Wright Middle 885 North Imperial Ave. Imperial 92251
202 13631646008569 Frank M. Wright Middle 885 North Imperial Ave. Imperial 92251
203 13631646008569 Frank M. Wright Middle 885 North Imperial Ave. Imperial 92251
204 13631646117808 T.L. Waggoner Elementary School 627 Joshua Tree St. Imperial 92251
205 13631646117808 T.L. Waggoner Elementary School 627 Joshua Tree St. Imperial 92251
206 13631646117808 T.L. Waggoner Elementary School 627 Joshua Tree St. Imperial 92251
207 13631646117808 T.L. Waggoner Elementary School 627 Joshua Tree St. Imperial 92251
208 47704586050926 Seiad Elementary 44539 Highway 96 Seiad Valley 96086-0647
209 47704586050926 Seiad Elementary 44539 Highway 96 Seiad Valley 96086-0647
210 47704586050926 Seiad Elementary 44539 Highway 96 Seiad Valley 96086-0647
211 47704586050926 Seiad Elementary 44539 Highway 96 Seiad Valley 96086-0647
212 47704586050926 Seiad Elementary 44539 Highway 96 Seiad Valley 96086-0647
213 33752003330529 Murrieta Valley High School 42200 Nighthawk Way Murrieta 92562
214 33752003330529 Murrieta Valley High School 42200 Nighthawk Way Murrieta 92562
215 33671573331014 Nuview Bridge Early College HS 30401 Reservoir Ave. Nuevo, Riverside County 92567
216 37680986038178 Central 122 W. 4th Avenue Escondido 920254134
217 37680986038194 Del Dios Middle School 1400 W. 9th Avenue Escondido 920292204
218 37680986038202 Felicita School 737 W. 13th Avenue Escondido 920255510
219 37680986066997 Miller School 1975 Miller Avenue Escondido 920255932
220 37680986093215 Hidden Valley Middle School 2700 Reed Road Escondido 920274223
221 37680986107882 L. R. Green School 3115 Las Palmas Avenue Escondido 920257300
222 33751926112551 Temecula Valley Charter School 35755 Abelia St Winchester 92596
223 33751923330917 Temecula Preparatory 35777 Abelia St Winchester 92596
224 47703346050785 Happy Camp Elementary 114 Park Way Happy Camp 96039-0467
225 47703346050785 Happy Camp Elementary 114 Park Way Happy Camp 96039-0467
226 43693936046544 Castlemont Elementary School 3040 Payne Ave. Campbell 95008
227 19753411937143 Redondo Union High School One Sea Hawk Way Redondo Beach 90277
228 19753416021992 Adams Middle 2600 Ripley Avenue Redondo Beach 90278
229 19753416022156 Washington Elementary 1100 Lilienthal Redondo Beach 90278

230 rows × 5 columns


In [15]:
ann_reps.iloc[1]


Out[15]:
Local Education Agency (LEA)                             Cecil Avenue Math & Science Academy
LEA Code                                                                      15634046009351
LEA Address                                                                 1405 12th Avenue
LEA City                                                                              Delano
LEA ZIP Code                                                                           93215
Energy Expenditure Plan (EEP) ID Number                                                   28
EEP Submission Fiscal Year                                                           2014-15
Reporting ID Number                                                                       18
Grant Amount Spent for Energy Manager                                                    NaN
Grant Amount Spent for Training                                                          NaN
Total Spent on Project Cost                                                           282756
Total Prop 39 Share Spent on Project Implementation                                   243156
Site Name                                                        Cecil Avenue Charter School
Site CDS Code                                                                 15634046009351
Site Address                                                               1430 Cecil Avenue
Site City                                                                             Delano
Site ZIP Code                                                                          93215
Total Spent for Implementation at Site                                                282756
Total Prop 39 Funds Spent for Implementation at Site                                  243156
Prop 39 Funds Spent for all EEMs at Site                                              243156
Prop 39 Funds Spent for all PV Measures at Site                                          NaN
Prop 39 Funds Spent for all PPA Measures at Site                                         NaN
Energy Efficiency Measure (EEM)                         HVAC-Retrocommissioning (Continuous)
Amount Spent for EEM                                                                   29609
AC Power Rating of the LEA Owned PV System (kW)                                          NaN
Amount Spent for PV Measure                                                              NaN
PPA PV System Size                                                                       NaN
Amount Spent for PPA Measure                                                             NaN
Name: 1, dtype: object

In [20]:
app_eeps['Site CDS Code'].nunique(), ann_reps['Site CDS Code'].nunique(), fin_reps['Site CDS Code'].nunique(),


Out[20]:
(3042, 1196, 76)

In [23]:
app_eeps.columns


Out[23]:
Index([u'Local Education Agency (LEA)', u'LEA Code', u'LEA Mailing Address',
       u'LEA City', u'LEA ZIP Code',
       u'Energy Expenditure Plan (EEP) ID Number',
       u'EEP Submission Fiscal Year',
       u'Grant Amount Requested Based on Single Year Allocation or Multiple Years of Allocation?',
       u'Grant Amount Requested (Prop 39 Shares for all sites plus Energy Manager & Training funds)',
       u'Grant Amount Requested through EEP for Energy Manager',
       u'Grant Amount Requested Through EEP for Training',
       u'Total Project Cost for Project Measure Implementation',
       u'LEA Tier Classification',
       u'Were Planning Funds Requested from California Department of Education?',
       u'Budget for Screening and Energy Audits Over Program Life (CDE Planning Funds)',
       u'Budget for Prop 39 Program Assistance Over Program Life (CDE Planning Funds)',
       u'Budget for Energy Manager Over Program Life (CDE Planning Funds)',
       u'Budget for Training Over Program Life (CDE Planning Funds)',
       u'LEA-Wide Savings to Investment Ratio (SIR) for EEP', u'Site Name',
       u'Site CDS Code', u'Site Mailing Address', u'Site City',
       u'Site ZIP Code', u'Senate District', u'Assembly District',
       u'Site Conditioned Area, Square Feet (SF)', u'Site Electric Utility',
       u'Site Natural Gas Utility', u'Average Peak Demand (kW)',
       u'Total Annual Electric Use for Site (kWh)',
       u'Annual On Site Generation (kWh)',
       u'Annual Electricity Purchase from Utility (kWh)',
       u'Annual Combined Electric Charges', u'Annual Natural Gas Use (therms)',
       u'Annual Natural Gas Charges', u'Annual Propane Use (Gal)',
       u'Annual Propane Charges', u'Annual Fuel Oil Use (Gal)',
       u'Annual Fuel Oil Charges', u'Electric Power Density (W/SF)',
       u'Electricity Density (kWh/SF)', u'Electricity Cost Density ($/SF)',
       u'Natural Gas Density (therms/SF)', u'Natural Gas Cost Density ($/SF)',
       u'Other Fuels Density (Gal/SF)', u'Other Fuels Cost Density ($/SF)',
       u'Combined Energy Costs Density ($/SF)',
       u'Energy Use Intensity (EUI) (kBtu/SF/Year)',
       u'Estimated Project Start Date', u'Estimated Project Completion Date',
       u'Estimated Total Project Cost for Site', u'Total Rebates Used at Site',
       u'Total Other Non-Repayable Funds Used at Site',
       u'Total Prop 39 Share Used at Site',
       u'Prop 39 Share Used for Energy Efficiency Measures at Site',
       u'Prop 39 Share Used for PV Measures at Site',
       u'Prop 39 Share Used for PPA Measures at Site',
       u'Total Demand Savings at Site (kW)',
       u'Total Annual Electric Savings at Site (kWh)',
       u'Total Annual Natural Gas Savings at Site (therms)',
       u'Total Annual Propane Savings at Site (therms)',
       u'Total Annual Fuel Oil Savings at Site (therms)',
       u'Total Annual Cost Savings at Site',
       u'Site Savings to Investment Ratio',
       u'Energy Efficiency Measures Category (EEM)',
       u'EEM Estimated Demand Savings (kW)',
       u'EEM Estimated Annual Electric Savings (kWh)',
       u'EEM Estimated Annual Natural Gas Savings (therms)',
       u'EEM Estimated Annual Propane Savings (Gal)',
       u'EEM Estimated Annual Fuel Oil Savings (Gal)',
       u'EEM Estimated Annual Energy Cost Savings',
       u'EEM Estimated Measure Cost', u'Estimated Rebates for EEM',
       u'Estimated Other Non-Repayable Funds for EEM',
       u'Estimated Total Rebates Plus Other Non-Repayable Funds for EEM',
       u'EEM SIR', u'AC Power Rating of the LEA Owned PV System (kW)',
       u'Estimated Effective Useful Life of PV Measure (years)',
       u'Estimated Demand Savings for PV Measure (kW)',
       u'Estimated First Year Annual Electricity Production of PV Measure (kWh)',
       u'Estimated First Year Annual Energy Cost Savings for PV Measure',
       u'Estimated PV Measure Cost', u'Estimated PV Measure Rebates',
       u'Estimated PV Measure Other Non-Repayable Funds',
       u'Estimated Total Rebates Plus Other Non-Repayable Funds for PV Measure',
       u'PV Measure SIR',
       u'AC Power Rating of PPA Measure generation system (kW)',
       u'Estimated Site Demand Savings of PPA Measure (kW)',
       u'Estimated First Year Electricity Production of PPA Measure Generation System (kWh)',
       u'Term of PPA Measure Agreement',
       u'Estimated PPA Measure Electricity Generation as Percentage of Baseline Electricity Usage (%)',
       u'Estimated First Year Electricity Cost ($/kWh)',
       u'Electricity Price Discount Offered in First Year (%)',
       u'PPA Measure Electricity Price Escalation Rate (%)',
       u'Net Present Value of Utility Cost Savings',
       u'Net Present Value of Payment to PPA Vendor',
       u'Net Present Value of Prop 39 Funds for PPA Measure',
       u'PPA Measure SIR'],
      dtype='object')

In [28]:
app_eeps.shape, app_eeps[[c for c in fields if c in app_eeps.columns]].drop_duplicates().shape


Out[28]:
((11577, 99), (3152, 5))

Find geographical location (lat, lon) for each site


In [78]:
# import googlemaps

import geocoder
g = geocoder.google('1770 East Eighth Street, Chico, CA')
g.latlng


Out[78]:
[]

In [79]:
g


Out[79]:
<[OVER_QUERY_LIMIT] Google - Geocode>

In [64]:
sites = app_eeps[[c for c in fields if c in app_eeps.columns]].drop_duplicates()
sites['Site CDS Code'] = sites['Site CDS Code'].apply(lambda x: str(int(x)) if not np.isnan(x) else x)
sites['Site Mailing Address'] = sites['Site Mailing Address'] + ', ' + sites['Site City'] + ', CA ' + sites['Site ZIP Code']
sites.head()


Out[64]:
Site CDS Code Site Name Site City Site Mailing Address Site ZIP Code
0 1100170000000 Burke Center Hayward 313 W.Winton Ave, Hayward, CA 94544 94544
3 1611196090054 Lincoln Middle School Alameda 1250 Fernside , Alameda, CA 94501 94501
8 1611190132878 Encinal High School Alameda 210 Cental Ave, Alameda, CA 94501 94501
11 1611196110779 Bay Farm Elementary School Alameda 200 Aughinbaugh Way , Alameda, CA 94502 94502
13 1611196090005 Donald Lum Elemenary School Alameda 1801 Sandcreek Way, Alameda, CA 94501 94501

In [41]:
# sites['Location'] = sites['Site Mailing Address'].apply(lambda x: geocoder.google(x).latlng)

In [67]:
len(sites), sites['Site CDS Code'].nunique(), sites['Site Mailing Address'].nunique()


Out[67]:
(3152, 3042, 3076)

In [135]:
# watch out with the 2500 requests/day quota for the free tier access to Google Maps API!

# locations = {}

for i,addr in enumerate(sites['Site Mailing Address'].unique()):
    clear_output(wait=True)
    print i, '/', len(sites)
    if addr not in locations:
        g = geocoder.google(addr)
        if g.ok:
            locations[addr] = tuple(g.latlng)
            print addr, ":", g.latlng
        else:
            print "Error in geocoding", addr

In [136]:
len(locations)


Out[136]:
3076

In [129]:
locations_df = pd.DataFrame(locations).T\
    .reset_index()\
    .rename(columns={0:"lat", 1:"lon", "index":'Site Mailing Address'})\
    .iloc[1:]

In [130]:
sites_df = pd.merge(sites, locations_df, how="left", on="Site Mailing Address")

In [131]:
sites_df.count()


Out[131]:
Site CDS Code           3151
Site Name               3151
Site City               3151
Site Mailing Address    3151
Site ZIP Code           3151
lat                     3150
lon                     3150
dtype: int64

In [132]:
import sys  
reload(sys)  
sys.setdefaultencoding('utf8')

for c,t in sites_df.dtypes.iteritems():
    if t == object:
        print c
        sites_df[c] = sites_df[c].apply(lambda x: x.encode('utf-8').strip() if not pd.isnull(x) else x)

In [137]:
sites_df.to_csv("../../data/prop-39-school/prop39-processed/site_information.csv", index=False)

In [134]:
sites_df


Out[134]:
Site CDS Code Site Name Site City Site Mailing Address Site ZIP Code lat lon
0 1100170000000 Burke Center Hayward 313 W.Winton Ave, Hayward, CA 94544 94544 37.658469 -122.098358
1 1611196090054 Lincoln Middle School Alameda 1250 Fernside , Alameda, CA 94501 94501 37.751768 -122.232073
2 1611190132878 Encinal High School Alameda 210 Cental Ave, Alameda, CA 94501 94501 37.772765 -122.289009
3 1611196110779 Bay Farm Elementary School Alameda 200 Aughinbaugh Way , Alameda, CA 94502 94502 37.744121 -122.251922
4 1611196090005 Donald Lum Elemenary School Alameda 1801 Sandcreek Way, Alameda, CA 94501 94501 37.761699 -122.260174
5 1611190126656 Maya Lin Elementary School Alameda 825 Taylor Ave, Alameda, CA 94501 94501 37.772710 -122.271669
6 1611190130229 Alameda High School Alameda 2201 Encinal Ave, Alameda, CA 94501 94501 37.764359 -122.247336
7 1611196090047 Henry Height Elementary School Alameda 2025 Santa Clara Ave, Alameda, CA 95401 95401 37.770183 -122.250221
8 1611196090112 Will C. Wood Middle School Alameda 420 Grand St, Alameda, CA 94501 94501 37.761327 -122.262452
9 1611196090021 Otis Elementary School Alameda 3010 Fillmore St, Alameda, CA 94501 94501 37.754225 -122.238538
10 1611196090120 Paden Elementary School Alameda 444 Cental Ave, Alameda, CA 94501 94501 37.771179 -122.283608
11 1611190111765 Ruby Bridges Elementary School Alameda 351 Jack London Ave, Alameda, CA 94501 94501 37.782532 -122.286370
12 1611196090039 Franklin Elementary School Alameda 1433 San Antonio Ave, Alameda, CA 94501 94501 37.768860 -122.261127
13 1612006089338 Rancho Las Positas Livermore 401 E. Jack London Blvd, Livermore, CA 94551 94551 37.688354 -121.796617
14 1612006001291 Michell K-8 Livermore 1001 Elaine Avenue, Livermore, CA 94551 94551 37.669138 -121.783536
15 1612006001317 Marylin Avenue Livermore 800 Marylin Avenue, Livermore, CA 94551 94551 37.684574 -121.786081
16 1612000134536 Livermore High School Livermore 400 Maple St, Livermore, CA 94550 94550 37.681790 -121.763773
17 1612006001234 Junction Avenue K-8 Livermore 298 Junction Avenue, Livermore, CA 94550 94550 37.687582 -121.766303
18 1612006001283 Jackson Avenue Livermore 554 Jackson Avenue, Livermore, CA 94551 94551 37.685054 -121.741472
19 1612006071906 Arroyo Seco Livermore 5280 Irene Way, Livermore, CA 94551 94551 37.684577 -121.728159
20 1612000130393 Almond Avenue Livermore 1401 Almond Ave, Livermore, CA 94550 94550 37.677102 -121.740856
21 1612006001341 Mendenhall Middle Livermore 1701 El Padro Drive, Livermore, CA 94551 94551 37.663332 -121.793116
22 1612006117543 Altamont Creek Livermore 6500 Garaventa Ranch Road, Livermore, CA 94551 94551 37.722706 -121.718839
23 1612000133397 Granada High Livermore 600 Wall St., Livermore, CA 94551 94551 37.672694 -121.789934
24 1612006085773 Christensen Middle Livermore 5757 Haggin Oaks Ave, Livermore, CA 94551 94551 37.725480 -121.728702
25 1612006110332 Croce Livermore 5650 Scenic Avenue, Livermore, CA 94551 94551 37.718035 -121.728311
26 1612000132670 Del Valle High Livermore 2253 5th St, Livermore, CA 94551 94551 37.678642 -121.766644
27 1612000000000 District Office Livermore 685 E. Jack London Blvd, Livermore, CA 94551 94551 37.688380 -121.791654
28 1612006001242 East Avenue Middle Livermore 3951 East Avenue, Livermore, CA 94551 94551 37.679571 -121.750460
29 1612000000000 Maintenance Grounds Livermore 2900 Ladd Avenue, Livermore, CA 94551 94551 37.687116 -121.763321
... ... ... ... ... ... ... ...
3122 49706496051635 Cinnabar Charter Petaluma 286 Skillman Ln., Petaluma, CA 94952-1226 94952-1226 38.258198 -122.665602
3123 12629270000000 Loleta Elementary School Loleta 700 Loleta Drive, Loleta, CA 95551-0547 95551-0547 40.643478 -124.218815
3124 9618466005482 Camino Elementary Camino 3060 Snows Rd., Camino, CA 95709-9578 95709-9578 38.732864 -120.676627
3125 50712336053011 Roberts Ferry Union Elementary Waterford 101 Roberts Ferry Rd., Waterford, CA 95386-9501 95386-9501 37.638505 -120.618754
3126 24656806025456 El Nido Elementary El Nido 161 East El Nido Rd., El Nido, CA 95317-9762 95317-9762 37.134018 -120.488632
3127 18641130000000 JESD Susanville 704-795 Bangham Ln., Susanville, CA 96130-7716 96130-7716 40.384542 -120.588980
3128 31750850128561 Rocklin Independent Charter Academy Rocklin 3250 Victory Dr., Rocklin, CA 95765-4992 95765-4992 38.815769 -121.254427
3129 10622651033703 Kings Canyon Continuation Dinuba 10026 S. Crawford Ave, Dinuba, CA 93618 93618 36.588704 -119.376468
3130 10622650119834 KC KIDS Pre-School Reedley 1280 East Washington Ave., Reedley, CA 93654-3595 93654-3595 36.591483 -119.435585
3131 10622650105692 A. L. Conner Elementary Orange Cove 222 Fourth St., Orange Cove, CA 93646-2111 93646-2111 36.629720 -119.307957
3132 10622656006738 Alta Elementary Reedley 21771 East Parlier Ave., Reedley, CA 93654-9535 93654-9535 36.610948 -119.398564
3133 10622656006761 General Grant Middle Reedley 360 North East Ave., Reedley, CA 93654-2510 93654-2510 36.599767 -119.444227
3134 10622656006787 Jefferson Elementary Reedley 1037 East Duff Ave., Reedley, CA 93654-3505 93654-3505 36.592590 -119.437969
3135 10622656006803 Lincoln Elementary Reedley 374 East North Ave., Reedley, CA 93654-2535 93654-2535 36.600560 -119.444960
3136 10622656006829 Navelencia Middle Reedley 22620 East Wahtoke Ave., Reedley, CA 93654-9604 93654-9604 36.684248 -119.385349
3137 10622651035310 Reedley High Reedley 740 West North Ave., Reedley, CA 93654-2418 93654-2418 36.602331 -119.455666
3138 10622656006845 Sheridan Elementary Orange Cove 1001 Ninth St., Orange Cove, CA 93646-2311 93646-2311 36.618811 -119.316604
3139 10622650116640 Dunlap Leadership Academy Dunlap 39500 Dunlap Road, Dunlap, CA 93621 93621 36.738022 -119.120479
3140 49708706066344 Olivet Elementary Charter Santa Rosa 1825 Willowside Rd., Santa Rosa, CA 95401-3923 95401-3923 38.464231 -122.807265
3141 49708706109144 Morrice Schaefer Charter Santa Rosa 1370 San Miguel Ave., Santa Rosa, CA 95403-1986 95403-1986 38.473962 -122.745639
3142 49708700101253 Jack London Elementary Santa Rosa 2707 Francisco Ave., Santa Rosa, CA 95403-1869 95403-1869 38.480641 -122.764448
3143 49708700000000 Piner-Olivet Union School District Office Santa Rosa 3450 Coffey Ln., Santa Rosa, CA 95403-1919 95403-1919 38.473690 -122.745927
3144 49708706113492 Piner-Olivet Charter Santa Rosa 2707 Francisco Ave., Santa Rosa, CA 95403-1869 95403-1869 38.480641 -122.764448
3145 19647336018204 Montague Charter Academy Arleta 13000 Montague St., Arleta, CA 91331-4146 91331-4146 34.244465 -118.415015
3146 40687260000000 Cayucos Elementary School District Cayucos 301 Cayucos Dr., Cayucos, CA 93430-1036 93430-1036 35.451828 -120.904538
3147 16101656069298 Kings County Special Education Hanford 959 Katie Hammond Ln., Hanford, CA 93230-4334 93230-4334 36.337720 -119.630500
3148 16101651630193 Kings County Community Hanford 146 Highland St., Hanford, CA 93230-6172 93230-6172 36.310568 -119.645955
3149 16101650000000 Lemoore Service Center Lemoore 876 E. D Street, Lemoore, CA 93245 93245 36.299612 -119.768121
3150 16101650000000 Administration Office - Information System Hanford 443 Greenfield Ave., Hanford, CA 93230 93230 36.328984 -119.657842
3151 36678763630993 Provisional Accelerated Learning Academy San Bernardino 2450 Blake St, San Bernardino, CA 92407 92407 34.151409 -117.356835

3152 rows × 7 columns


In [ ]: